package team.scau.laboratory_repair.modules.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import team.scau.laboratory_repair.common.base.BaseRepository;
import team.scau.laboratory_repair.modules.analysis.piechart.PieDataBean;
import team.scau.laboratory_repair.modules.po.Record;

import java.math.BigInteger;
import java.util.List;

public interface    RecordRepository  extends BaseRepository<Record, Long> {

    @Query(value="select count(e.id),IFNULL(c.name,'未知') " +
            "from lab_record e left join lab_repair_classify c on e.classify=c.id " +
            "GROUP by e.classify ORDER BY e.classify ASC"
            ,nativeQuery=true)
    List<Object[]> listTypeCountBar();

    @Query(value="select count(e.id) as value,IFNULL(c.name,'未知') as name" +
            " from lab_record e left join lab_repair_classify c on e.classify=c.id" +
            " GROUP by e.classify ORDER BY e.classify ASC",nativeQuery=true)
    List<Object[]> listTypeCountPie();

    @Query(value="select count(e.id),IFNULL(e.registrant_type,'未知')" +
            " from lab_record e " +
            " GROUP by e.registrant_type"
            ,nativeQuery=true)
    List<Object[]> listRegistrantCount();

    @Query(value="select count(e.id),IFNULL(c.name,'未知')" +
            " from lab_record e left join lab_area c on e.area=c.id " +
            " GROUP by e.registrant_type,c.name"
            ,nativeQuery=true)
    List<Object[]> listAreaCount();

}
